import pymysql

# 建立数据库连接
connection = pymysql.connect(
    host='localhost',
    user='root',
    password='hhp20040512',
    database='crawlerDemo'
)

try:
    # 创建游标对象
    cursor = connection.cursor()

    # 创建表
    create_table_query = '''
    CREATE TABLE IF NOT EXISTS employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100),
        age INT,
        department VARCHAR(100)
    )
    '''
    cursor.execute(create_table_query)

    # 插入数据
    insert_query = "INSERT INTO employees (name, age, department) VALUES (%s, %s, %s)"
    employees_data = [
        ('John Doe', 30, 'IT'),
        ('Jane Smith', 35, 'HR'),
        ('Mark Johnson', 28, 'Sales')
    ]
    cursor.executemany(insert_query,employees_data)

    # 提交事务
    connection.commit()

    # 执行查询
    select_query = "SELECT * FROM employees"
    cursor.execute(select_query)

    # 获取查询结果的所有行数据
    rows = cursor.fetchall()

    # 打印查询结果
    for row in rows:
        print(row)

finally:
    # 关闭游标对象和数据库连接
    cursor.close()
    connection.close()